# coding=utf-8
import re

from sqlalchemy import create_engine, Column, Integer, DateTime, Boolean, \
    String, Float, TEXT, Date, TIMESTAMP, text
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
DB_URL = 'mysql+mysqldb://upwc:Ease1234@rm-m5ed54w933j6s7zvao.mysql.rds.aliyuncs.com:3306/upwc?charset=utf8'

engine = create_engine(DB_URL, encoding='utf-8')

Base = declarative_base()

DB_SESSION = sessionmaker(bind=engine, autocommit=True)
session = DB_SESSION()


class Task(Base):
    __tablename__ = 'task'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer)
    platform_id = Column(Integer)
    product_code = Column(String, default='')
    app_id = Column(Integer)
    app_user_id = Column(Integer)
    start_time = Column(DateTime)
    end_time = Column(DateTime)
    status = Column(Integer)
    create_time = Column(DateTime)
    modify_time = Column(DateTime)
    is_deleted = Column(Boolean)
    phase = Column(String(10))
    phase_status = Column(String(12))
    callback_code = Column(String(12))
    callback_times = Column(Integer)
    run_times = Column(Integer)
    account_balance = Column(String(10))
    account_bank_number = Column(Integer)
    real_name = Column(String)
    id_card = Column(String)
    auth_history = Column(String, default='')
    version = Column(Integer, default=1)


class CallbackLog(Base):
    __tablename__ = 'callback_log'
    id = Column(Integer, primary_key=True)
    app_id = Column(Integer)
    task_id = Column(Integer)
    callback_status = Column(Integer)
    callback_data = Column(LONGTEXT, default='')
    callback_time = Column(TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
    callback_code = Column(Integer, default=0)
    callback_url = Column(String)


class App(Base):
    __tablename__ = 'app'
    id = Column(Integer, primary_key=True)
    nickname = Column(String, default='')
    password = Column(String, default='')
    tel = Column(String, default='')
    balance = Column(String)
    logo = Column(String)
    total_account = Column(String)
    status = Column(Integer)
    last_login_time = Column(DateTime)
    login_count = Column(Integer)
    last_login_ip = Column(String)
    token = Column(String)
    api_key = Column(String)
    callback_url = Column(String)


class Order(Base):
    __tablename__ = 'order'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    platform_id = Column(Integer)
    order_id = Column(String)
    platform_user_id = Column(String)
    restaurant_name = Column(String)
    restaurant_tel = Column(String)
    user_name = Column(String)
    user_tel = Column(String)
    items = Column(TEXT)
    status = Column(String)
    discount = Column(Float)
    discount_info = Column(TEXT)
    packing_fee = Column(Float)
    amount = Column(Float)
    order_start_time = Column(String)
    order_end_time = Column(String)
    deliver_address = Column(String)
    deliver_fee = Column(Float)
    delivery_company = Column(String)
    rider_name = Column(String)
    rider_tel = Column(String)
    pay_method = Column(String)
    remark = Column(String)
    crawl_time = Column(DateTime)


class CallDetail(Base):
    __tablename__ = 'call_detail'
    id = Column(Integer, primary_key=True)
    phone = Column(String)
    task_id = Column(Integer)
    product_code = Column(String)
    call_type = Column(String)
    opposite_phone = Column(String)
    begin_time = Column(String)
    call_duration = Column(String)
    called_city = Column(String)
    calling_city = Column(String)
    base_fee = Column(String)
    info_fee = Column(String)
    other_fee = Column(String)
    total_fee = Column(String)
    crawl_time = Column(DateTime)
    real_name = Column(String)
    id_card = Column(String)


class LoanUserInfo(Base):
    __tablename__ = 'loan_user'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    product_code = Column(String)
    user_id = Column(String)
    real_name = Column(String)
    card_id = Column(String)
    phone = Column(String)
    limit_amount = Column(String)
    credit_score = Column(Float)
    repayment_status = Column(String)
    work_info = Column(LONGTEXT)
    house_info = Column(LONGTEXT)
    contacts = Column(LONGTEXT)
    id_card_images = Column(TEXT)
    other_infos = Column(LONGTEXT)


class CreditInfo(Base):
    __tablename__ = 'loan_info'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    product_code = Column(String)
    user_id = Column(String)
    amount = Column(Float)
    rate = Column(Float)
    life_loan = Column(Integer)
    loan_status = Column(String)
    plan_repayment_period = Column(LONGTEXT)
    plan_repayment_amount = Column(LONGTEXT)
    repayment_period = Column(LONGTEXT)
    repayment_status = Column(String)
    owe_amount = Column(Float)
    order_id = Column(String)
    loan_time = Column(DateTime)
    overdue_day = Column(Integer)
    demurrage = Column(Float)
    repayment_amount = Column(Float)
    loan_date = Column(Date)
    overdue_fine = Column(Float)
    lender_card_id = Column(String)
    management_fee = Column(Float)
    service_fee = Column(Float)


class LoanTradeRecord(Base):
    __tablename__ = 'loan_trade_record'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    product_code = Column(String)
    user_id = Column(String)
    amount = Column(Float)
    oper_content = Column(String)
    trade_time = Column(DateTime)
    trade_status = Column(String)
    trade_type = Column(String)


class RepaymentStream(Base):
    __tablename__ = 'loan_repayment_stream'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    product_code = Column(String)
    user_id = Column(String)
    order_id = Column(String)
    transactional_num = Column(String)
    repayment_amount = Column(Float)
    repayment_date = Column(Date)
    repayment_status = Column(String)


class CarrierBasicItem(Base):
    __tablename__ = 'carrier_basic'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    cell_phone = Column(String)
    idcard = Column(String)
    reg_time = Column(DateTime)
    real_name = Column(String)


class CallRecord(Base):
    __tablename__ = 'call_record'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    start_time = Column(DateTime)
    init_type = Column(String)
    use_time = Column(DateTime)
    place = Column(String)
    other_cell_phone = Column(String)
    cell_phone = Column(String)
    datasource = Column(String)
    subtotal = Column(String)
    call_type = Column(String)


class NetRecord(Base):
    __tablename__ = 'net_record'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    start_time = Column(DateTime)
    subflow = Column(String)
    flow_info = Column(String)
    use_time = Column(String)
    place = Column(String)
    net_type = Column(String)
    cell_phone = Column(String)
    subtotal = Column(Float)


class CarrierTransaction(Base):
    __tablename__ = 'carrier_transaction'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    total_amt = Column(Float)
    plan_amt = Column(Float)
    cell_phone = Column(String)
    bill_cycle = Column(DateTime)
    pay_amt = Column(Float)


class SMSRecord(Base):
    __tablename__ = 'sms_record'
    id = Column(Integer, primary_key=True)
    task_id = Column(Integer)
    place = Column(String)
    other_cell_phone = Column(String)
    subtotal = Column(Float)
    start_time = Column(DateTime)
    cell_phone = Column(String)
    init_type = Column(String)


emoji_pattern = re.compile(
    u"(\ud83d[\ude00-\ude4f])|"  # emoticons
    u"(\ud83c[\udf00-\uffff])|"  # symbols & pictographs (1 of 2)
    u"(\ud83d[\u0000-\uddff])|"  # symbols & pictographs (2 of 2)
    u"(\ud83d[\ude80-\udeff])|"  # transport & map symbols
    u"(\ud83c[\udde0-\uddff])"  # flags (iOS)
    "+", flags=re.UNICODE)


def remove_emoji(text):
    return emoji_pattern.sub(r'', text)


def refresh():
    # 刷新数据库，完成插入操作
    session.query(App.id).first()
